package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.Course;
import com.xmy.cultivate.entity.OrderApply;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.views.ResTransaction;
import com.xmy.cultivate.excel.ReturnPremiumDailySheet;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 订单报名信息 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-07-28
 */
@Mapper
public interface OrderApplyMapper extends BaseMapper<OrderApply> {


    @Select("SELECT * FROM order_view ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "affirmTypeList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderApplyAffirmMapper.getAffirmTypeListForOrderId")
            )
    })
    public abstract IPage<OrderApply> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("SELECT IFNULL( SUM( price ), 0 ) AS price FROM order_view ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "affirmTypeList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderApplyAffirmMapper.getAffirmTypeListForOrderId")
            )
    })
    public abstract OrderApply getOrderApplySum(@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("SELECT * FROM order_view where id = ${id}")
    public abstract OrderApply getOneForId(@Param("id") Long id);


    /**
     *
     * @param orderNo
     * @param orderType 订单类型：1报名，2补费，3转课，4退费，5转校
     * @return
     */
    @Select("SELECT * FROM order_apply where order_no = ${orderNo} and order_type = ${orderType}")
    public abstract OrderApply getOneForOrderNo(@Param("orderNo") String orderNo,@Param("orderType") Integer orderType);


    /**
     * 欠费列表
     * @param wrapper
     * @return
     */
    @Select("select id,getDictName(order_type, 13) as orderTypeName,IFNULL(handle_person_name,'') as handlePersonName,handle_date as handleDate,getSchoolName(handle_school_id) as handleSchoolName,own_price as ownPrice from order_apply ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "course",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderApplyMapper.getOrderDetailListForOrderId")
            )
    })
    public abstract List<Map<String,Object>> orderArrears(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select id,course_name from order_detail where order_id = #{orderId}")
    public abstract List<Map<String,Object>> getOrderDetailListForOrderId(@Param("orderId") Long orderId);





    @Select("select * from expenses_and_receipts_view ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "orderPayAccountList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderPayAccountMapper.getListForOrderId")
            )
    })
    public abstract IPage<OrderApply> expensesAndReceipts(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    @Select("select * from transaction_view ${ew.customSqlSegment}")
    @Results({
            @Result(column = "order_id",property = "order_id"),
            @Result(
                    property = "orderPayAccountList",
                    column = "order_id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderPayAccountMapper.getListForOrderId")
            )
    })
    public abstract IPage<ResTransaction> transaction(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select * from affirm_income_view ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "orderPayAccountList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderPayAccountMapper.getListForOrderId")
            ),
            @Result(
                    property = "course",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderApplyMapper.getOrderDetailListForOrderId")
            ),
            @Result(
                    property = "affirmTypeList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderApplyAffirmMapper.getAffirmTypeListForOrderId")
            )
    })
    public abstract IPage<OrderApply> affirmIncome(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 退费日报表
     *
     * @return
     */
    @Select({"<script>",
            "select id,order_id,student_name,receipt_no,school_id,student_id,handle_date,handle_person_name,year_class_id,is_print,year_part,quarter_num,cause_content, ",
            "getSchoolName(school_id) AS school_name,",
            "SUM( out_house ) AS out_house,SUM( out_price ) AS out_price,subjects_id, ",
            "getStaffNameForId(teacher_id) AS teacher_name ",
            "FROM (",
            "SELECT ",
            "t3.id,t2.order_id,t3.handle_date,t3.student_id,t3.receipt_no,t3.school_id,t1.out_house,t1.out_price,t2.subjects_id,t2.year_class_id,t3.is_print,t4.`name` AS student_name,t3.handle_person_name,t5.teacher_id,t2.year_part,t2.quarter_num,t6.cause_content ",
            "FROM ",
            "order_transfer_detail AS t1 ",
            "INNER JOIN order_detail AS t2 ON t1.old_order_detail_id = t2.id ",
            "INNER JOIN order_apply AS t3 ON t1.order_id = t3.id ",
            "LEFT JOIN student t4 ON t3.student_id = t4.id ",
            "LEFT JOIN order_return_teacher AS t5 ON t5.order_id = t1.order_id ",
            "LEFT JOIN order_return_cause AS t6 ON t6.order_id = t1.order_id ",
            "WHERE ",
            "t3.lesson_type = 1 ",
            "AND t3.deleted = 0 ",
            "AND t3.`status` &lt;&gt; 2 ",
                "AND (t3.order_type = 4 AND t3.apply_type =${orderType})",
            "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
                "AND t3.handle_date BETWEEN '${startDate}' AND '${endDate}'",
            "</when>",
            "<when test='schoolIdList!=null and schoolIdList!=&apos;&apos;'>",
            "AND t3.school_id IN (${schoolIdList}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t2.subjects_id IN (${subjectsId}) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND t2.year_class_id IN (${yearClassId}) ",
            "</when>",
            "<when test='isPrint!=null and isPrint!=&apos;&apos;'>",
            "AND t3.is_print = ${isPrint} ",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos;'>",
            "AND t4.`name` LIKE '%${studentName}%' ",
            "</when>",
            "<when test='status!=null and status!=&apos;&apos;'>",
            "AND t3.affirm_type = ${status} ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND t5.teacher_id = ${teacherId} ",
            "</when>",
            ") AS t ",
            "GROUP BY id order by id desc",
            "</script>"})
    public abstract IPage<ReturnPremiumDailySheet> getReturnPremiumDailySheet(IPage<?> page, @Param("orderType") String orderType, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("schoolIdList") String schoolIdList, @Param("subjectsId") String subjectsId, @Param("yearClassId") String yearClassId, @Param("isPrint") String isPrint, @Param("studentName") String studentName, @Param("status") String status, @Param("teacherId") String teacherId);

    /**
     * 退费日报表
     *
     * @return
     */
    @Select({"<script>",
            "SELECT SUM(out_price) AS out_price FROM ( ",
            "select id,student_name,receipt_no,school_id,student_id,handle_date,handle_person_name,year_class_id,is_print, ",
            "getSchoolName(school_id) AS school_name,",
            "SUM( out_house ) AS out_house,SUM( out_price ) AS out_price,subjects_id ",
            "FROM (",
            "SELECT ",
            "t3.id,t3.handle_date,t3.student_id,t3.receipt_no,t3.school_id,t1.out_house,t1.out_price,t2.subjects_id,t2.year_class_id,t3.is_print,t4.`name` AS student_name,t3.handle_person_name ",
            "FROM ",
            "order_transfer_detail AS t1 ",
            "INNER JOIN order_detail AS t2 ON t1.old_order_detail_id = t2.id ",
            "INNER JOIN order_apply AS t3 ON t1.order_id = t3.id ",
            "LEFT JOIN student t4 ON t3.student_id = t4.id ",
            "LEFT JOIN order_return_teacher AS t5 ON t5.order_id = t2.order_id ",
            "WHERE ",
            "t3.lesson_type = 1 ",
            "AND t3.deleted = 0 ",
            "AND t3.`status` &lt;&gt; 2 ",
            "AND t3.order_type = 4 AND t3.apply_type = ${orderType}",
            "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND t3.handle_date BETWEEN '${startDate}' AND '${endDate}'",
            "</when>",
            "<when test='schoolIdList!=null and schoolIdList!=&apos;&apos;'>",
            "AND t3.school_id IN (${schoolIdList}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t2.subjects_id IN (${subjectsId}) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND t2.year_class_id IN (${yearClassId}) ",
            "</when>",
            "<when test='isPrint!=null and isPrint!=&apos;&apos;'>",
            "AND t3.is_print = ${isPrint} ",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos;'>",
            "AND t4.`name` LIKE '%${studentName}%' ",
            "</when>",
            "<when test='status!=null and status!=&apos;&apos;'>",
            "AND t3.affirm_type = ${status} ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND t5.teacher_id = ${teacherId} ",
            "</when>",
            ") AS t ",
            "GROUP BY id order by id desc",
            ") AS t2 ",
            "</script>"})
    public abstract BigDecimal getReturnPremiumPrice(@Param("orderType") String orderType, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("schoolIdList") String schoolIdList, @Param("subjectsId") String subjectsId, @Param("yearClassId") String yearClassId, @Param("isPrint") String isPrint, @Param("studentName") String studentName, @Param("status") String status, @Param("teacherId") String teacherId);


    @Select("select count(id) as count from order_apply where school_id =${schoolId} and year_part = ${yearPart} and quarter_num = ${quarterNum} and student_id = ${studentId} and subjects_id_str = #{subjectsId} and order_type = 1 and deleted = 0 and `status` <>2 and lesson_type =1 and `status` = 0")
    public Integer getNotAffirmCount(@Param("yearPart") Integer yearPart,
                                     @Param("quarterNum") Integer quarterNum,
                                     @Param("schoolId") Long schoolId,
                                     @Param("studentId") Long studentId,
                                     @Param("subjectsId") String subjectsId);

    @Select("select GROUP_CONCAT(student_name) as student_name from (select getStudentNameForId(student_id) as student_name from order_apply where school_id =${schoolId} and year_part = ${yearPart} and quarter_num = ${quarterNum} and student_id in (${studentId}) and subjects_id_str in(${subjectsId}) and order_type = 1 and deleted = 0 and `status` <>2 and lesson_type =1 and `status` = 0 GROUP BY student_id) as t")
    public String getNotAffirmStudentName(@Param("yearPart") Integer yearPart,
                                     @Param("quarterNum") Integer quarterNum,
                                     @Param("schoolId") Long schoolId,
                                     @Param("studentId") String studentId,
                                     @Param("subjectsId") String subjectsId);

    @Select("select GROUP_CONCAT(student_name) as student_name from (select getStudentNameForId(student_id) as student_name from order_apply where school_id =${schoolId} and year_part = ${yearPart} and quarter_num = ${quarterNum} AND student_id IN (select student_id from student_grade where grade_id = ${gradeId} and status =1 and reading_status in(0,1,2,3,4, 5,6) and deleted = 0 GROUP BY student_id) and subjects_id_str in(${subjectsId}) and order_type = 1 and deleted = 0 and `status` <>2 and lesson_type =1 and `status` = 0 GROUP BY student_id) as t")
    public String getNotAffirmStudentNameForGradeId(@Param("yearPart") Integer yearPart,
                                                    @Param("quarterNum") Integer quarterNum,
                                                    @Param("schoolId") Long schoolId,
                                                    @Param("subjectsId") String subjectsId,
                                                    @Param("gradeId") Long gradeId);

}
